{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 1\n",
    "\n",
    "Read in the three data frames, but without setting an index. Ensure that the column names in `oecd_tourism_df` are `abbrev`, `TIME`, and `Value`, and that the `dtype` of the `Value` column is `np.int64`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "oecd_df = pd.read_csv('../data/oecd_locations.csv', header=None,\n",
    "                     names=['abbrev', 'country'])\n",
    "\n",
    "oecd_tourism_df = pd.read_csv('../data/oecd_tourism.csv',\n",
    "                             usecols=[0, 5,6],\n",
    "                              header=0,\n",
    "                             names=['abbrev', 'TIME', 'Value'])\n",
    "\n",
    "wine_df = pd.read_csv('../data/winemag-150k-reviews.csv', \n",
    "                      usecols=['country', 'points'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 2\n",
    "\n",
    "Perform the same joins as before, but using `merge`, rather than `join`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country\n",
       "Australia          37634.433333\n",
       "Austria            16673.886364\n",
       "Belgium            16525.237545\n",
       "Brazil             13942.913958\n",
       "Canada             32593.612500\n",
       "Denmark            10362.563636\n",
       "Finland             5288.658591\n",
       "France             58228.804000\n",
       "Germany            75011.823091\n",
       "Hungary             5108.871591\n",
       "Israel              6634.454042\n",
       "Italy              39539.560000\n",
       "Japan              28606.891667\n",
       "Korea              21677.131818\n",
       "United Kingdom     63507.159091\n",
       "United States     171847.083333\n",
       "Name: Value, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tourism_spending = (oecd_df\n",
    "                    .merge(oecd_tourism_df, on='abbrev')\n",
    "                    .groupby('country')['Value'].mean()\n",
    "                   )\n",
    "tourism_spending"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country\n",
       "Albania                   88.000000\n",
       "Argentina                 85.996093\n",
       "Australia                 87.892475\n",
       "Austria                   89.276742\n",
       "Bosnia and Herzegovina    84.750000\n",
       "Brazil                    83.240000\n",
       "Bulgaria                  85.467532\n",
       "Canada                    88.239796\n",
       "Chile                     86.296768\n",
       "China                     82.000000\n",
       "Croatia                   86.280899\n",
       "Cyprus                    85.870968\n",
       "Czech Republic            85.833333\n",
       "Egypt                     83.666667\n",
       "England                   92.888889\n",
       "France                    88.925870\n",
       "Georgia                   85.511628\n",
       "Germany                   88.626427\n",
       "Greece                    86.117647\n",
       "Hungary                   87.329004\n",
       "India                     87.625000\n",
       "Israel                    87.176190\n",
       "Italy                     88.413664\n",
       "Japan                     85.000000\n",
       "Lebanon                   85.702703\n",
       "Lithuania                 84.250000\n",
       "Luxembourg                87.000000\n",
       "Macedonia                 84.812500\n",
       "Mexico                    84.761905\n",
       "Moldova                   84.718310\n",
       "Montenegro                82.000000\n",
       "Morocco                   88.166667\n",
       "New Zealand               87.554217\n",
       "Portugal                  88.057685\n",
       "Romania                   84.920863\n",
       "Serbia                    87.714286\n",
       "Slovakia                  83.666667\n",
       "Slovenia                  88.234043\n",
       "South Africa              87.225421\n",
       "South Korea               81.500000\n",
       "Spain                     86.646589\n",
       "Switzerland               87.250000\n",
       "Tunisia                   86.000000\n",
       "Turkey                    88.096154\n",
       "US                        87.818789\n",
       "US-France                 88.000000\n",
       "Ukraine                   84.600000\n",
       "Uruguay                   84.478261\n",
       "Name: points, dtype: float64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_points = (\n",
    "    wine_df\n",
    "    .groupby('country')['points'].mean()\n",
    ")\n",
    "\n",
    "country_points"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>points</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>87.892475</td>\n",
       "      <td>37634.433333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Austria</th>\n",
       "      <td>89.276742</td>\n",
       "      <td>16673.886364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>83.240000</td>\n",
       "      <td>13942.913958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada</th>\n",
       "      <td>88.239796</td>\n",
       "      <td>32593.612500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>France</th>\n",
       "      <td>88.925870</td>\n",
       "      <td>58228.804000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Germany</th>\n",
       "      <td>88.626427</td>\n",
       "      <td>75011.823091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hungary</th>\n",
       "      <td>87.329004</td>\n",
       "      <td>5108.871591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Israel</th>\n",
       "      <td>87.176190</td>\n",
       "      <td>6634.454042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Italy</th>\n",
       "      <td>88.413664</td>\n",
       "      <td>39539.560000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>85.000000</td>\n",
       "      <td>28606.891667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              points         Value\n",
       "country                           \n",
       "Australia  87.892475  37634.433333\n",
       "Austria    89.276742  16673.886364\n",
       "Brazil     83.240000  13942.913958\n",
       "Canada     88.239796  32593.612500\n",
       "France     88.925870  58228.804000\n",
       "Germany    88.626427  75011.823091\n",
       "Hungary    87.329004   5108.871591\n",
       "Israel     87.176190   6634.454042\n",
       "Italy      88.413664  39539.560000\n",
       "Japan      85.000000  28606.891667"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    country_points.to_frame()\n",
    "    .merge(tourism_spending, on='country')\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>points</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albania</th>\n",
       "      <td>88.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Argentina</th>\n",
       "      <td>85.996093</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Australia</th>\n",
       "      <td>87.892475</td>\n",
       "      <td>37634.433333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Austria</th>\n",
       "      <td>89.276742</td>\n",
       "      <td>16673.886364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bosnia and Herzegovina</th>\n",
       "      <td>84.750000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brazil</th>\n",
       "      <td>83.240000</td>\n",
       "      <td>13942.913958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bulgaria</th>\n",
       "      <td>85.467532</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Canada</th>\n",
       "      <td>88.239796</td>\n",
       "      <td>32593.612500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chile</th>\n",
       "      <td>86.296768</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>China</th>\n",
       "      <td>82.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Croatia</th>\n",
       "      <td>86.280899</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cyprus</th>\n",
       "      <td>85.870968</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Czech Republic</th>\n",
       "      <td>85.833333</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Egypt</th>\n",
       "      <td>83.666667</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>England</th>\n",
       "      <td>92.888889</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>France</th>\n",
       "      <td>88.925870</td>\n",
       "      <td>58228.804000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Georgia</th>\n",
       "      <td>85.511628</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Germany</th>\n",
       "      <td>88.626427</td>\n",
       "      <td>75011.823091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Greece</th>\n",
       "      <td>86.117647</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hungary</th>\n",
       "      <td>87.329004</td>\n",
       "      <td>5108.871591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>India</th>\n",
       "      <td>87.625000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Israel</th>\n",
       "      <td>87.176190</td>\n",
       "      <td>6634.454042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Italy</th>\n",
       "      <td>88.413664</td>\n",
       "      <td>39539.560000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japan</th>\n",
       "      <td>85.000000</td>\n",
       "      <td>28606.891667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lebanon</th>\n",
       "      <td>85.702703</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lithuania</th>\n",
       "      <td>84.250000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Luxembourg</th>\n",
       "      <td>87.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Macedonia</th>\n",
       "      <td>84.812500</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mexico</th>\n",
       "      <td>84.761905</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Moldova</th>\n",
       "      <td>84.718310</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Montenegro</th>\n",
       "      <td>82.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Morocco</th>\n",
       "      <td>88.166667</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Zealand</th>\n",
       "      <td>87.554217</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Portugal</th>\n",
       "      <td>88.057685</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Romania</th>\n",
       "      <td>84.920863</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Serbia</th>\n",
       "      <td>87.714286</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slovakia</th>\n",
       "      <td>83.666667</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slovenia</th>\n",
       "      <td>88.234043</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Africa</th>\n",
       "      <td>87.225421</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Korea</th>\n",
       "      <td>81.500000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spain</th>\n",
       "      <td>86.646589</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Switzerland</th>\n",
       "      <td>87.250000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tunisia</th>\n",
       "      <td>86.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Turkey</th>\n",
       "      <td>88.096154</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US</th>\n",
       "      <td>87.818789</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>US-France</th>\n",
       "      <td>88.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ukraine</th>\n",
       "      <td>84.600000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uruguay</th>\n",
       "      <td>84.478261</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Belgium</th>\n",
       "      <td>NaN</td>\n",
       "      <td>16525.237545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Denmark</th>\n",
       "      <td>NaN</td>\n",
       "      <td>10362.563636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Finland</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5288.658591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Korea</th>\n",
       "      <td>NaN</td>\n",
       "      <td>21677.131818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Kingdom</th>\n",
       "      <td>NaN</td>\n",
       "      <td>63507.159091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>NaN</td>\n",
       "      <td>171847.083333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           points          Value\n",
       "country                                         \n",
       "Albania                 88.000000            NaN\n",
       "Argentina               85.996093            NaN\n",
       "Australia               87.892475   37634.433333\n",
       "Austria                 89.276742   16673.886364\n",
       "Bosnia and Herzegovina  84.750000            NaN\n",
       "Brazil                  83.240000   13942.913958\n",
       "Bulgaria                85.467532            NaN\n",
       "Canada                  88.239796   32593.612500\n",
       "Chile                   86.296768            NaN\n",
       "China                   82.000000            NaN\n",
       "Croatia                 86.280899            NaN\n",
       "Cyprus                  85.870968            NaN\n",
       "Czech Republic          85.833333            NaN\n",
       "Egypt                   83.666667            NaN\n",
       "England                 92.888889            NaN\n",
       "France                  88.925870   58228.804000\n",
       "Georgia                 85.511628            NaN\n",
       "Germany                 88.626427   75011.823091\n",
       "Greece                  86.117647            NaN\n",
       "Hungary                 87.329004    5108.871591\n",
       "India                   87.625000            NaN\n",
       "Israel                  87.176190    6634.454042\n",
       "Italy                   88.413664   39539.560000\n",
       "Japan                   85.000000   28606.891667\n",
       "Lebanon                 85.702703            NaN\n",
       "Lithuania               84.250000            NaN\n",
       "Luxembourg              87.000000            NaN\n",
       "Macedonia               84.812500            NaN\n",
       "Mexico                  84.761905            NaN\n",
       "Moldova                 84.718310            NaN\n",
       "Montenegro              82.000000            NaN\n",
       "Morocco                 88.166667            NaN\n",
       "New Zealand             87.554217            NaN\n",
       "Portugal                88.057685            NaN\n",
       "Romania                 84.920863            NaN\n",
       "Serbia                  87.714286            NaN\n",
       "Slovakia                83.666667            NaN\n",
       "Slovenia                88.234043            NaN\n",
       "South Africa            87.225421            NaN\n",
       "South Korea             81.500000            NaN\n",
       "Spain                   86.646589            NaN\n",
       "Switzerland             87.250000            NaN\n",
       "Tunisia                 86.000000            NaN\n",
       "Turkey                  88.096154            NaN\n",
       "US                      87.818789            NaN\n",
       "US-France               88.000000            NaN\n",
       "Ukraine                 84.600000            NaN\n",
       "Uruguay                 84.478261            NaN\n",
       "Belgium                       NaN   16525.237545\n",
       "Denmark                       NaN   10362.563636\n",
       "Finland                       NaN    5288.658591\n",
       "Korea                         NaN   21677.131818\n",
       "United Kingdom                NaN   63507.159091\n",
       "United States                 NaN  171847.083333"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    country_points.to_frame()\n",
    "    .merge(tourism_spending, on='country', how='outer')\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Beyond 3\n",
    "\n",
    "How is the default `merge` different from the default `join`, when it comes to `NaN` values?\n",
    "\n",
    "By default, `join` performs a \"left join,\" meaning that there might be `NaN` values in the column(s) from the right side. However, `merge` performs an \"inner join\" by default, meaning that it uses the intersection of the indexes from the right and left. As a result, `NaN` values won't occur thanks to the join (but they might come in thanks to `NaN` values in the input frames."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
